''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' Jinfeng Zhuang @ 2024.8.14
'
' 单个操作
'     WriteSingle 写入一个寄存器
'     ReadSingle
'
' 批量操作
'     WriteAll
'     ReadAll
'
' 事件
'     Worksheet_Change
'       写单独一个寄存器在这里触发
'     Worksheet_BeforeDoubleClick
'       SCAN, WRITE ALL, READ SINGLE 都是这里触发的
'
' 状态更新
'     UpdateTcpStatus
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Const CELL_CONF_IP      As String = "B2"
Private Const CELL_CONF_IF      As String = "C2"

Private Const CELL_CMD          As String = "A1"
Private Const CELL_CONNECT      As String = "A2"
Private Const CELL_SCAN         As String = "D3"
Private Const CELL_EXECUTE      As String = "E3"
Private Const CELL_EXECUTE_S    As String = "B3"
Private Const CELL_EXECUTE_E    As String = "C3"

Dim RetVal                      As Integer
Dim FlagConnected               As Integer
Dim RetRegValue                 As String

' 写入一个寄存器
Private Sub WriteSingle(ByVal row As Integer)
        Cells(row, 5).Interior.ColorIndex = 3 ' 红色
        
        ' 构造协议包
        Range(CELL_CMD).Value = "w " + Cells(row, 1) + " " + Cells(row, 2) + " " + Cells(row, 3) + " " + Cells(row, 5)
        
        ' 发送协议包
        TCPSend Range(CELL_CMD).Value
        
        ' 调试信息
        Debug.Print Range(CELL_CMD).Value
        
        ' 根据协议，需要接收应答
        RetRegValue = TCPRecv
        
        ' 更新对应的读寄存器值
        Cells(row, 4).Value = RetRegValue
        
        Cells(row, 5).Interior.ColorIndex = 0 ' 黑色
End Sub

' 读一个寄存器的值
Private Sub ReadSingle(ByVal row As Integer)
        Cells(row, 4).Interior.ColorIndex = 3 ' 红色

        ' 构造协议包
        Range(CELL_CMD).Value = "r " + Cells(row, 1) + " " + Cells(row, 2) + " " + Cells(row, 3)
        
        ' 发送协议包
        TCPSend Range(CELL_CMD).Value
        
        ' 调试信息
        Debug.Print Range(CELL_CMD).Value
        
        ' 接收应答
        RetRegValue = TCPRecv
        
        ' 填写应答数据
        Cells(row, 4) = RetRegValue
        
        Cells(row, 4).Interior.ColorIndex = 0 ' 黑色
End Sub

' Start -> End
Private Sub WriteAll(ByVal a As Integer, ByVal b As Integer)
    Dim i As Integer
    
    Range(CELL_EXECUTE).Interior.ColorIndex = 3 ' 红色
    
    For i = a To b
        WriteSingle i
        Sleep 100
    Next i
    
    Range(CELL_EXECUTE).Interior.ColorIndex = 0 ' 黑色
End Sub

' 刷新指定范围的寄存器
Private Sub ReadAll(ByVal a As Integer, ByVal b As Integer)
    Dim i As Integer
    
    Range(CELL_SCAN).Interior.ColorIndex = 3
    
    For i = a To b
        ReadSingle i
        Sleep 100
    Next i
    
    Range(CELL_SCAN).Interior.ColorIndex = 0
End Sub

' 鼠标双击事件触发，在默认操作触发前
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If CELL_CONNECT = Target.Address(0, 0) Then
        If "Connected" = Target.Text Then
            TCP_Disconnect
        Else
            TCP_Connect Range(CELL_CONF_IP).Value
            
            Debug.Print Range(CELL_CONF_IP).Value
            
        End If
        
        UpdateTcpStatus
    End If
    
    If "Connected" = Range(CELL_CONNECT).Value Then
    
        ' Write All
        If CELL_EXECUTE = Target.Address(0, 0) Then
            WriteAll Range(CELL_EXECUTE_S).Value, Range(CELL_EXECUTE_E).Value
            UpdateTcpStatus
        
        ' SCAN
        ElseIf CELL_SCAN = Target.Address(0, 0) Then
            ReadAll Range(CELL_EXECUTE_S).Value, Range(CELL_EXECUTE_E).Value
            UpdateTcpStatus
        
        ' 触发读寄存器
        ElseIf Target.Column = 4 Then
            Target.Interior.ColorIndex = 3
            
            If Target.row >= Range(CELL_EXECUTE_S).Value And Target.row <= Range(CELL_EXECUTE_E).Value Then
            
                Range(CELL_CMD).Value = "r " + Target.Offset(0, -3).Text + " " + Target.Offset(0, -2).Text + " " + Target.Offset(0, -1).Text
                
                Debug.Print Range(CELL_CMD).Value
                
                TCPSend Range(CELL_CMD).Value
                
                Sleep 200 ' There is 100ms delay between send and recv internal
                
                RetRegValue = TCPRecv
                
                Target.Value = RetRegValue
                
                UpdateTcpStatus
            Else
                MsgBox "Not In Range"
            End If
            
            Target.Interior.ColorIndex = 0
        End If
        
    End If
    
End Sub

' 更新 TCP 状态
Private Sub UpdateTcpStatus()

    RetVal = TCPStatus()
    
    Debug.Print "TCP Status: " & RetVal
    
    If False = RetVal Then
        Range(CELL_CONNECT).Value = "Disconnected"
        Range(CELL_CONNECT).Interior.ColorIndex = 0
    Else
        Range(CELL_CONNECT).Value = "Connected"
        Range(CELL_CONNECT).Interior.ColorIndex = 3
    End If
    
End Sub

' 单元格发生变化，即写入数据了
Private Sub Worksheet_Change(ByVal Target As Range)
    If "Connected" = Range(CELL_CONNECT).Value Then
        If Target.Column = 5 Then
            Target.Interior.ColorIndex = 3
            If Target.row >= Range(CELL_EXECUTE_S).Value And Target.row <= Range(CELL_EXECUTE_E).Value Then
            
                Range(CELL_CMD).Value = "w " + Target.Offset(0, -4).Text + " " + Target.Offset(0, -3).Text + " " + Target.Offset(0, -2).Text + " " + Target.Text
                
                TCPSend Range(CELL_CMD).Value
                
                Debug.Print Range(CELL_CMD).Value
                
                Sleep 200 ' There is 100ms delay between send and recv internal
                
                RetRegValue = TCPRecv
                
                Cells(Target.row, 4).Value = RetRegValue
                
                UpdateTcpStatus
            End If
            Target.Interior.ColorIndex = 0
        End If
    End If
End Sub

